package com.coderman.generateSqlBatchTools.service.impl;

import com.coderman.generateSqlBatchTools.bean.ColumnBean;
import com.coderman.generateSqlBatchTools.service.GenerateHelper;
import com.coderman.generateSqlBatchTools.service.IGenerateSqlTools;
import com.coderman.generateSqlBatchTools.utils.PoiExcelUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;

import java.util.Map;

/**
 * Created by ${fanchunshuai} on 2017-4-8.
 *
 * @version: V1.0
 * @Desc: 实现基于xlsx的sql生成服务接口
 * @Copyright (c) 2017 58到家-版权所有
 */
public class GenerateSqlToolsXLSXImpl implements IGenerateSqlTools {
    private Logger logger = Logger.getLogger(GenerateSqlToolsXLSImpl.class);

    @Override
    public boolean generateInsertBatchSql(String excelFilePath, int outDataType, String outPath, int fromIndex, Map<String, ColumnBean> columnBeanMap, String tableName) throws Exception {
        try {
            //这里默认读取sheetIndex = 0，也就是第一页的数据
            XSSFSheet sheet = PoiExcelUtils.readExcelSheet(excelFilePath, 0);
            int rows = 0;
            rows = sheet.getPhysicalNumberOfRows();

            if (sheet == null) {
                logger.error("excel sheet object is null ,please check the excel ...");
                return false;
            }

            //参数检查
            if (!GenerateHelper.checkParams(fromIndex, rows, tableName, columnBeanMap)) {
                return false;
            }

            StringBuilder sqlBuilder = new StringBuilder("insert into ");

            StringBuilder valuebuilder = new StringBuilder(" values ");

            StringBuilder columnBuilder = new StringBuilder("(");
            for (int i = fromIndex; i < rows; i++) {
                XSSFRow row = sheet.getRow(i);
                if (null == row) {
                    continue;
                }
                StringBuilder builder = new StringBuilder("(");

                for (Map.Entry<String, ColumnBean> entry : columnBeanMap.entrySet()) {
                    ColumnBean columnBean = entry.getValue();
                    if (i == fromIndex) {
                        columnBuilder.append(columnBean.getColumnName());
                        columnBuilder.append(", ");
                    }

                    String value = PoiExcelUtils.getStringCellValue(row.getCell(columnBean.getIndex()));
                    if (StringUtils.isNotBlank(value)) {
                        if (columnBean.getDataType() == ColumnBean.ColumnDataType.BIGINT.getType()) {
                            long lValue = Long.parseLong(value);
                            builder.append(lValue);
                            builder.append(", ");
                        }
                        if (columnBean.getDataType() == ColumnBean.ColumnDataType.INT.getType()) {
                            int iValue = Integer.parseInt(value);
                            builder.append(iValue);
                            builder.append(", ");
                        }
                        if (columnBean.getDataType() == ColumnBean.ColumnDataType.BOOLEAN.getType()) {
                            boolean bValue = Boolean.parseBoolean(value);
                            builder.append(bValue);
                            builder.append(", ");
                        }
                        if (columnBean.getDataType() == ColumnBean.ColumnDataType.VARCHAR.getType()) {
                            builder.append("'");
                            builder.append(value);
                            builder.append("' ,");
                        }
                    }
                }
                valuebuilder.append(builder.substring(0, builder.length() - 2));
                valuebuilder.append("),\n");
            }

            String columnStr = columnBuilder.substring(0, columnBuilder.length() - 2) + ")";
            String valueStr = valuebuilder.substring(0, valuebuilder.length() - 2);
            sqlBuilder.append(columnStr).append("  ").append(valueStr);
            return GenerateHelper.resultDataManage(outDataType, sqlBuilder, outPath);
        } catch (Exception e) {
            logger.error("generate sql failed...", e);
        } finally {
            PoiExcelUtils.close();
        }
        return false;
    }


    @Override
    public boolean generateDeleteBatchSql(String excelFilePath, int outDataType, String outPath, int fromIndex, Map<String, ColumnBean> columnBeanMap, String tableName) {
        int rows;
        try {
            //这里默认读取sheetIndex = 0，也就是第一页的数据
            XSSFSheet sheet = PoiExcelUtils.readExcelSheet(excelFilePath, 0);
            rows = sheet.getPhysicalNumberOfRows();
            StringBuilder batchBuilder = new StringBuilder();
            //参数检查
            if(!GenerateHelper.checkParams(fromIndex,rows,tableName,columnBeanMap)){
                return false;
            }
            for (int k = fromIndex; k < rows; k++) {
                XSSFRow row = sheet.getRow(k);
                StringBuilder sqlBuilder = new StringBuilder("delete from ");
                sqlBuilder.append(tableName);
                sqlBuilder.append(" where ");
                for (Map.Entry<String, ColumnBean> entry : columnBeanMap.entrySet()) {
                    ColumnBean columnBean = entry.getValue();

                    String value = PoiExcelUtils.getStringCellValue(row.getCell(columnBean.getIndex()));

                    if(StringUtils.isNotBlank(value)){
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BIGINT.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            long lValue = Long.parseLong(value);
                            sqlBuilder.append(lValue);
                            sqlBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.INT.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            int iValue = Integer.parseInt(value);
                            sqlBuilder.append(iValue);
                            sqlBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BOOLEAN.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            boolean bValue = Boolean.parseBoolean(value);
                            sqlBuilder.append(bValue);
                            sqlBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.VARCHAR.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            sqlBuilder.append("'");
                            sqlBuilder.append(value);
                            sqlBuilder.append("' and ");
                        }
                    }
                }
                String sql = sqlBuilder.substring(0,sqlBuilder.length() - 5);
                sqlBuilder = new StringBuilder(sql);
                sqlBuilder.append(";\n");
                batchBuilder.append(sqlBuilder);
            }

            //对生成的sql语句进行处理
            return GenerateHelper.resultDataManage(outDataType,batchBuilder,outPath);

        } catch (Exception e) {
            logger.error("is't failed ,the process to generate sqlbatch occurs exception ",e);

        }finally {
            PoiExcelUtils.close();
        }
        return false;

    }



    @Override
    public boolean generateUpdateBatchSql(String excelFilePath, int outDataType, String outPath, int fromIndex, Map<String, ColumnBean> columnBeanMap, Map<String, ColumnBean>  whereBeanMap, String tableName) {
        int rows;

        try {
            //这里默认读取sheetIndex = 0，也就是第一页的数据
            XSSFSheet sheet = PoiExcelUtils.readExcelSheet(excelFilePath, 0);
            rows = sheet.getPhysicalNumberOfRows();
            StringBuilder batchBuilder = new StringBuilder();

            //参数检查
            if(!GenerateHelper.checkParams(fromIndex,rows,tableName,columnBeanMap)){
                logger.error("columnBeanMap is empty ,please check the param list ...  ");
                return false;
            }

            for (int k = fromIndex; k < rows; k++) {
                StringBuilder sqlBuilder = new StringBuilder("update ");
                sqlBuilder.append(tableName);
                sqlBuilder.append(" set ");

                for (Map.Entry<String, ColumnBean> entry : columnBeanMap.entrySet()) {
                    ColumnBean columnBean = entry.getValue();
                    XSSFRow row = sheet.getRow(k);
                    String value = PoiExcelUtils.getStringCellValue(row.getCell(columnBean.getIndex()));

                    if(StringUtils.isNotBlank(value)){
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BIGINT.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            long lValue = Long.parseLong(value);
                            sqlBuilder.append(lValue);
                            sqlBuilder.append(" , ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.INT.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            int iValue = Integer.parseInt(value);
                            sqlBuilder.append(iValue);
                            sqlBuilder.append(" , ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BOOLEAN.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            boolean bValue = Boolean.parseBoolean(value);
                            sqlBuilder.append(bValue);
                            sqlBuilder.append(" , ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.VARCHAR.getType()){
                            sqlBuilder.append(columnBean.getColumnName());
                            sqlBuilder.append(" = ");
                            sqlBuilder.append("'");
                            sqlBuilder.append(value);
                            sqlBuilder.append("' , ");
                        }
                    }
                }

                StringBuilder whereBuilder = new StringBuilder(" where ");
                for (Map.Entry<String, ColumnBean> entry : whereBeanMap.entrySet()) {
                    ColumnBean columnBean = entry.getValue();

                    XSSFRow row = sheet.getRow(k);
                    String value = PoiExcelUtils.getStringCellValue(row.getCell(columnBean.getIndex()));

                    if(StringUtils.isNotBlank(value)){
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BIGINT.getType()){
                            whereBuilder.append(columnBean.getColumnName());
                            whereBuilder.append(" = ");
                            long lValue = Long.parseLong(value);
                            whereBuilder.append(lValue);
                            whereBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.INT.getType()){
                            whereBuilder.append(columnBean.getColumnName());
                            whereBuilder.append(" = ");
                            int iValue = Integer.parseInt(value);
                            whereBuilder.append(iValue);
                            whereBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.BOOLEAN.getType()){
                            whereBuilder.append(columnBean.getColumnName());
                            whereBuilder.append(" = ");
                            boolean bValue = Boolean.parseBoolean(value);
                            whereBuilder.append(bValue);
                            whereBuilder.append(" and ");
                        }
                        if(columnBean.getDataType() == ColumnBean.ColumnDataType.VARCHAR.getType()){
                            whereBuilder.append(columnBean.getColumnName());
                            whereBuilder.append(" = ");
                            whereBuilder.append("'");
                            whereBuilder.append(value);
                            whereBuilder.append("' and ");
                        }
                    }
                }

                String sql = sqlBuilder.substring(0,sqlBuilder.length() - 2);
                sqlBuilder = new StringBuilder(sql);
                String whereStr = whereBuilder.substring(0,whereBuilder.length() - 5);
                sqlBuilder.append(" ");
                sqlBuilder.append(whereStr);
                sqlBuilder.append(";\n");
                batchBuilder.append(sqlBuilder);
            }

            //对生成的sql语句进行处理
            return GenerateHelper.resultDataManage(outDataType,batchBuilder,outPath);

        } catch (Exception e) {
            e.printStackTrace();
            logger.error("is't failed ,the process to generate sqlbatch occurs exception ",e);

        }finally {
            PoiExcelUtils.close();
        }
        return false;
    }
}
